#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table as
(
select
	num,
	null percent1,
	content,
	'本地行业人才需求量' name,
	df_time as operate_time
from (

	select
		df_time,
		content,
		sum(posts) as num
	from (
		select
			date_format(update_time,'yyyy') as df_time,
			business_type as content,
			posts
		from cy_database.dws_enterprise_position_require_detail
		)a
	where df_time is not null and content != 'null' and content is not null
	group by df_time,content
	)aa
),




tmp2_table as
(
select
	0 num,
	null percent1,
	'人才引进没有数据支持' content,
	'人才引进没有数据支持' name,
	'2020-06-17' operate_time

),


tmp3_table as
(
select
	num,
	null percent1,
	'本地劳动力总人数' content,
	'本地劳动力总人数' name,
	dt as operate_time
from (
	select
		dt,
		count(1) as num
	from cy_database.dws_person_detail
	where dt = '2020-06-17'
	group by dt
	)a
),



tmp4_table as
(
select
	num,
	null percent1,
	'每月就业人数' content,
	'每月就业人数' name,
	df_time as operate_time
from (
	select
		df_time,
		count(name) as num
	from (
		select
			name,
			date_format(entry_work,'yyyy-MM') as df_time
		from cy_database.dws_person_work_detail
		)a
	group by df_time
	)aa
),


tmp5_table as
(
select
	num,
	null percent1,
	'创业' content,
	'本地劳动人员就业状态' name,
	df_time as operate_time
from (
	select
		df_time,
		count(name) as num
	from (
		select
			date_format(update_time,'yyyy') as df_time,
			name
		from cy_database.dws_person_detail
		where startup_code is not null or startup_code != 'null'
		)a
	group by df_time
	)aa
),




tmp6_table as
(
select
	num,
	null percent1,
	content,
	'女-年龄段分布' as name,
	cast(current_date() as string) operate_time
from(

	select
		content,num
	from(
		select
			content,num,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num
			from (
				SELECT
					case when age <= 20 then '00后'
						when  20<age and age<= 30 then '90后'
						when 30<age and age<=40 then '80后'
						when 40<age and age<=50 then '70后'
						when 50<age and age<60 then '60后'
						else '60以后' end as content
				from cy_database.dws_person_detail pwd
				where sex ='0'
				)a
			)aa
	    )aaa
	where rn=1
	)b

union all
select
	num,
	null percent1,
	content,
	'男-年龄段分布' as name,
	cast(current_date() as string) operate_time
from(

	select
		content,num
	from(
		select
			content,num,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num
			from (
				SELECT
					case when age <= 20 then '00后'
						when  20<age and age<= 30 then '90后'
						when 30<age and age<=40 then '80后'
						when 40<age and age<=50 then '70后'
						when 50<age and age<60 then '60后'
						else '60以后' end as content
				from cy_database.dws_person_detail pwd
				where sex ='1'
				)a
			)aa
	    )aaa
	where rn=1
	)b

),


tmp7_table as
(

select
	num,
	null percent1,
	content,
	'人员流动' name,
	df_time as operate_time
from (
	select
		df_time,
		content,
		count(name) as num
	from (
		select
			date_format(update_time,'yyyy') as df_time,
			case when is_back = '0' then '返乡人员'
				when  is_back ='1' then '外出人员'
				end as content,
				name
		from cy_database.dws_out_work
		)a
	group by df_time,content
	)aa
),


tmp8_table as
(
select
	num,amount,
	cast((num/amount) as decimal(10,2)) as percent1,
	content,
	'文化程度' as name,
	cast(current_date() as string) operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when education = '1' or education = '2' or education = '3' then '中学及其他'
						when  education = '4' then '专科'
						when education = '5' then '本科'
						else '硕士级以上' end as content
				from cy_database.dws_person_detail
				)a
			)aa
	    )aaa
	where rn=1
	)b
)


insert overwrite table cy_database.ads_big_screen_result_changyuan
select num,percent1,content,name,operate_time from tmp1_table

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table

union all
select num,percent1,content,name,operate_time from tmp4_table

union all
select num,percent1,content,name,operate_time from tmp5_table

union all
select num,percent1,content,name,operate_time from tmp6_table

union all
select num,percent1,content,name,operate_time from tmp7_table

union all
select num,percent1,content,name,operate_time from tmp8_table
;

;

"
$hive -e "$sql"